import java.sql.*;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //查询
       // m1();
        //条件查询
        //m2();
        //增加
        //m3();
        //删除
        //m4();



    }

    private static void m2() {
        Connection conn =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;

        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入你要查询的姓名");
            String word = sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?severTimezone=GMT%2B8&useSSL=false";
            Connection connection = DriverManager.getConnection(url, "root", "wenzhou0619");
            //3.写sql
            String sql = "select * from sys_user where name like ?";
            //4.预编译sql
             preparedStatement = connection.prepareStatement(sql);

            //5.有参数设置参数
            preparedStatement.setObject(1,"%"+word+"%");//设置参数

            //6.执行sql
             resultSet = preparedStatement.executeQuery();

            //7.遍历结果集
            while (resultSet.next()){//获取
                int id = resultSet.getInt(1);//Int用数字是列数，String用列名
                String name = resultSet.getString(2);
                String pass = resultSet.getString(3);
                System.out.println(id+"-------"+name+"-------"+pass);

            }
            //8.管资源
            resultSet.close();
            preparedStatement.close();
            connection.close();


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //8.关闭资源
            try {
                if (resultSet !=null){
                    resultSet.close();
                }
                if (preparedStatement !=null){
                    preparedStatement.close();
                }
                if (conn !=null){
                    conn.close();
                }
            }catch (SQLException e){
                throw new RuntimeException(e);
            }

        }

    }

    private static void m3() {
        Connection conn =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;

        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入要增加的id");
            int id = sc.nextInt();
            System.out.println("请输入要新增的姓名");
            String name = sc.next();
            System.out.println("请输入要新增的密码");
            String pass = sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?severTimezone=GMT%2B8&useSSL=false";
            Connection connection = DriverManager.getConnection(url, "root", "wenzhou0619");
//            if(connection !=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql = "insert into sys_user(id,name,pass) values (?,?,?)";
            //4.预编译sql
            preparedStatement = connection.prepareStatement(sql);

            //5.有参数设置参数
            preparedStatement.setObject(1,id);
            preparedStatement.setObject(2,name);//设置参数
            preparedStatement.setObject(3,pass);

            //6.执行sql
            int result = preparedStatement.executeUpdate();

//            //7.遍历结果集
//            while (resultSet.next()){//获取
//                int id = resultSet.getInt(1);//Int用数字是列数，String用列名
//                String name = resultSet.getString(2);
//                String pass = resultSet.getString(3);
//                System.out.println(id+"-------"+name+"-------"+pass);
//
//            }
            if(result>0){
                System.out.println("新增成功");
            }
            //8.管资源
            //result.close();
            preparedStatement.close();
            connection.close();


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //8.关闭资源
            try {
                if (resultSet !=null){
                    resultSet.close();
                }
                if (preparedStatement !=null){
                    preparedStatement.close();
                }
                if (conn !=null){
                    conn.close();
                }
            }catch (SQLException e){
                throw new RuntimeException(e);
            }

        }


    }

    private static void m1() throws ClassNotFoundException, SQLException { //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取链接
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?severTimezone=GMT%2B8&useSSL=false", "root", "wenzhou0619");
        if (conn != null) {
            System.out.println("连接成功");
        }
        //3.编写sql语句
        String sql = "select * from sys_user";

        //4.预处理sql .var
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

        //5.如果有参数，设置参数

        //6.执行sql语句
        ResultSet resultSet = preparedStatement.executeQuery();

        //7.如果是查询就遍历结果/如果是增删改 判断是否成功
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String name = resultSet.getNString(2);
            String pass = resultSet.getNString(3);
            System.out.println(id + "-----" + name + "-----" + pass);
        }
        //8.关闭资源
        resultSet.close();
        preparedStatement.close();
        conn.close();

    }


    private static void m4() throws ClassNotFoundException, SQLException {

        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要删除");
        int id=sc.nextInt();
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?severTimezone=GMT%2B8&useSSL=false";
        Connection connection = DriverManager.getConnection(url, "root", "wenzhou0619");
//            if(connection !=null){
//                System.out.println("连接成功");
//            }
        //3.写sql
        String sql = "delete from sys_user where id=?";
        //4.预编译sql
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.有参数设置参数
        preparedStatement.setObject(1,id);//设置参数

        //6.执行sql
        int result = preparedStatement.executeUpdate();

        if(result>0){
            System.out.println("删除成功");
        }
        //8.管资源
        //result.close();
        preparedStatement.close();
        connection.close();

}
}



